package com.amado.easyui2.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.amado.easyui2.dao.UserDao;
import com.amado.easyui2.domain.User;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;

@Repository
public class UserDaoMysqlImpl implements UserDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Override
	public int add(User user) {
		String sql = "insert into sys_user(dept_num,user_num,user_name,user_password ,create_name,update_name)values(?,?,?,?,?,?)";
		return jdbcTemplate.update(sql,new Object[] { user.getDepartmentNum(), user.getUserNum(),user.getUserName(),user.getPassword(),user.getCreateName(),user.getUpdateName()});
	}

	@Override
	public int delete(Long id) {
//		Connection conn = DBConnection.getConn();
//		String sql = "update sys_user set delete_Flag='Y' where use_ID=?";
//		PreparedStatement ps = null;
//		try {
//			int i = 1;
//			ps = conn.prepareStatement(sql);
//			ps.setLong(i, id);
//			return ps.executeUpdate();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			DBConnection.close(null, ps, conn);
//		}
//		return 0;
		String sql = "update sys_user set delete_Flag='Y' where use_ID=?";
		return jdbcTemplate.update(sql,id);
	}
	@Override
	public int updatePassword(String userName, String password) {
		String sql = "update sys_user set user_password=? where user_Num =?";
		return jdbcTemplate.update(sql,password,userName);
	}

	@Override
	public int update(User user) {
		String sql = "update sys_user set dept_Num=?,user_Num=?,user_name=?,user_password=? where use_ID =?";
		return jdbcTemplate.update(sql,user.getDepartmentNum(),user.getUserNum(),user.getUserName(),user.getPassword(),user.getId());
	}
	
	@Override
	public int listAllCount(String userName,String departNum) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(1) FROM sys_user where delete_flag='N'";
		if (userName != null && userName.trim() != "") {
			sql = sql + " and user_Num like ?";
		}
		if (departNum != null && departNum.trim() != "") {
			sql = sql + " and dept_Num =?";
		}
		System.out.println("sql"+sql);
		List<User> list = new ArrayList<User>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (departNum != null && departNum.trim() != "") {
				ps.setString(j++, departNum);
			}
			if (userName != null && userName.trim() != "") {
				ps.setString(j++, "%"+userName+"%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}
	@Override
//	SELECT * FROM class t1 LEFT    JOIN student t2 on  t1.id=t2.classid ; 
	public List<User> listAll(String userName, String departNum, Page page) {
		StringBuilder sql = new StringBuilder("select t1.USE_ID,t1.MANAGE,t1.DEPT_NUM,t1.USER_NUM,t1.USER_NAME,t1.user_password,t1.CREATE_TIME,t1.UPDATE_TIME,t1.CREATE_NAME,t1.UPDATE_NAME,t1.DELETE_FLAG,t2.DEPT_NAME from sys_user t1 left join sys_dept t2 on t1.dept_Num=t2.dept_Num where t1.delete_flag='N'");
		System.out.println("sql"+sql);	
		List<Object> listParam = new ArrayList<>();
		if (departNum != null && departNum.trim() != "") {
			sql.append(" and t1.dept_Num =?");
			listParam.add(departNum);
		}
		if (userName != null && userName.trim() != "") {
			sql.append( " and t1.user_Num like ?");
			listParam.add("%"+userName+"%");
		}
		sql.append("limit ?,?");
		System.out.println("sql"+sql);		
		Integer start = (page.getCurrentPage()-1)*page.getRowSize();
		Integer rowSize = page.getRowSize();
		listParam.add(start);
		listParam.add(rowSize);
		return getJdbcTemplate().query(sql.toString(), listParam.toArray(), new UserDeptMapper());
	}

	@Override
	public  List<User> list() {
		String sql = "SELECT * FROM sys_user where delete_flag='N'";
		return jdbcTemplate.query(sql,new ShopRowMapper());
	}
	class ShopRowMapper implements RowMapper<User> {
		// rs为返回结果集，以每行为单位封装着
		  @Override
		public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			User user = new User();
			//dept_Num=?,user_Num=?,user_name=?,user_password=? where use_ID =?";
			user.setId(rs.getLong("use_id"));
			user.setManage(rs.getString("manage"));
			user.setDepartmentNum(rs.getString("dept_num"));
			user.setUserNum(rs.getString("user_num"));
			user.setUserName(rs.getString("user_name")); 
			user.setPassword(rs.getString("user_password"));
			user.setCreateName(rs.getString("create_name"));
			user.setCreateTime(rs.getTimestamp("create_time"));
			user.setUpdateName(rs.getString("update_name"));
			user.setUpdateTime(rs.getTimestamp("update_time"));
			return user;
		}
	}
	class UserDeptMapper extends ShopRowMapper {
		// rs为返回结果集，以每行为单位封装着
		  @Override
		public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			User user = super.mapRow(rs, rowNum);
			user.setDepartmentName(rs.getString("dept_name"));
			return user;
		}
	}

	@Override
	public List<User> login(User user)
	{
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM sys_user where user_Num=? and user_password=? ";
		List<User> list = new ArrayList<User>();
		try
		{
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				int i = 1;
				String userNum = rs.getString(i++);
				String password = rs.getString(i++);
				list.add(new User(userNum,password));
			}
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public User getBy(String userNum) {
		String sql="select * from sys_user where user_Num=?"; 
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql, new String[] { userNum }, new ShopRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public User getById(Long id) {
		String sql="select * from sys_user where use_id=?"; 
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql, new Object[] { id }, new ShopRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public Integer selectCountByDeptNumber(String deptNumber) {
		String sql="select count(1) from sys_user t where t.DEPT_NUM=? and t.DELETE_FLAG='N'"; 
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql,new String[] { deptNumber }, Integer.class);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
//
//	@Override
//	public User getByName(String userName) {
//		String sql="select count(1) from sys_user where userName=?";
//		try {
//			// 如果没有查询到数据会报异常EmptyResultDataAccessException
//			return getJdbcTemplate().queryForObject(sql, new String[] { userName }, new ShopRowMapper());
//		} catch (EmptyResultDataAccessException e) {
//			return null;
//		}
//	}
//	
//	@Override
//	public StudentEntity getBy(String userName, String password) {
//			String sql="select * from sys_user where userName=? and passWord=?"; 
//			try {
//				// 如果没有查询到数据会报异常EmptyResultDataAccessException
//				return getJdbcTemplate().queryForObject(sql, new String[] { userName,password }, new ShopRowMapper());
//			} catch (EmptyResultDataAccessException e) {
//				return null;
//			}
//	}
}
